--1.查询福建行所有学校（SIGN_INSID 是以350开头还是351开头）
select SHL_ID,SHL_NM,SIGN_INSID from shl_info where SIGN_INSID like'350%' and length(SHL_NM)>3;

--2.查询福建行开通了渠道的数据
select * from thdpty_app_info where shl_id in( select SHL_ID from shl_info where SIGN_INSID like'350%' and length(SHL_NM)>3);

--3.给所有开通了渠道的福建行学校新增闽政通渠道
insert into thdpty_App_info (APP_ID, SHL_ID, SHL_NM, APP_NM, TITLE, CATEGORY, KEY, OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG, YKT_CZ_WAY,WXXCX_FLAG,RCRD_TYPE)
       values ('MINZHENGTONG', '513401', '凉山州农业学校', '微信小程序', '0', '01', '39218dc300f7b53a8f29d1894257beec', '1', '1', '01', '1', '1000000000','1','00');

insert into thdpty_App_info (APP_ID, SHL_ID, SHL_NM, APP_NM, TITLE, CATEGORY, OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG, YKT_CZ_WAY,RCRD_TYPE)
select 'MINZHENGTONG',SHL_ID, SHL_NM,'闽政通','0','00','1','1','01','0','1000000000',RCRD_TYPE
from thdpty_App_info where app_id='wx796af3381abe60e0' and  shl_id in( select SHL_ID from shl_info where SIGN_INSID like'350%' and length(SHL_NM)>3)

select a.ST_ID,a.CST_NM,a.CRDT_NO,b.MBLPH_NO,b.EBNK_SIGN_ACCNO from stcust_info  a, stacct_info b   where a.svc_id=b.svc_id and a.shl_id='10574'  and b.Acc_TpCd!='07' and a.st_id = '20232005182';


--给山东财经大学燕山学院新增数币相关菜单
insert into SCHL_MODEL_TB(SHL_ID,APP_ID,MODEL_ID,Card_TPCD,MODEL_Wight,Display_type)
values('学校编码','APP_ID','菜单ID','卡类型ID','菜单权重','显示类型');

--设置校园卡充值支付方式（数币和电子钱包）
update THDPTY_APP_INFO set YKT_CZ_WAY='100000000000010000' where SHL_ID='学校编码' and App_id='App_id'

--给山东财经大学燕山学院新增数币商户信息
insert into pay_merchant_info(SHL_ID,mrch_id,mrch_name,counterno,mrch_tpcd,mrch_dpbkinno,pubkey,app_id,status)
    values('学校编码','商户号','商户名称','柜台号','商户类型','商户开户行号','商户公钥','APP_ID','商户状态');

--设置山东财经大学燕山学院付款码为数币二维码
update card_tb set rcrd_type='07' where shl_id='学校编码' and Card_TPCD='00';











--1.把现有银行卡绑定参数备份
create table yx_param_OTHERBANKFLAG_20250328 as select * from yx_param where param_no='OTHERBANKFLAG'

--2.1删除OTHERBANKFLAG参数且开通他人绑卡的学校
delete from yx_param where param_no='OTHERBANKFLAG' and shl_id in (select shl_id from shl_info  where other_qb_flag='1')


--2.2把存量没有维护参数学校新插入银行卡绑定位图参数（开通了他人绑卡的学校插入OTHERBANKFLAG值为2）
insert into yx_param(shl_id,app_id,param_no,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING)
select a.shl_id,a.app_id,'OTHERBANKFLAG','2','他行卡绑定','01','0' from thdpty_app_info a left join shl_info b on a.shl_id=b.shl_id where b.other_qb_flag='1';


--3.1删除OTHERBANKFLAG参数且为白名单模式的学校
delete from yx_param where param_no='OTHERBANKFLAG' and shl_id in (select shl_id from shl_info  where white_flag in(('7','8','12'))


--3.2插入OTHERBANKFLAG参数且为白名单模式的学校
insert into yx_param(shl_id,app_id,param_no,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING)
select a.shl_id,a.app_id,'OTHERBANKFLAG','2','他行卡绑卡,游客','01','0' from thdpty_app_info a left join shl_info b on a.shl_id=b.shl_id where b.white_flag in(('7','8','12');



select count(*)  from ( select shl_id,st_id,count(*) as count1 from xm_trad_flow where PLAT_TXN_DATE > '20240831' and PLAT_TXN_DATE < '20241231' and shl_id in ('10333','10610','10394','32058201','12789','10386','10393','11956','43038','13667','11053','11647','12867','10392','35021101','35012101','33021203','10316','13105','35020007','32011801','13106','12806','14160','32060027','32010021','14269','13945','14541','13288','32010019','14436','12792','10390','13383') and result='Y') where count1>=2

update stcsut_info set card_tb='01' where shl_id='temp6982091' and st_id=''

update shl_info set smkey='2bf9f59189f7aa42',cst_sm3_key='uNQsds8kKSBEnvH2O4LPxkdOGkTCAQJKMcDvfabtT6ib95iS',jrhx_sm3_key='nz4k3vzT575L43ITDp9csDXACidReGVEhB8ANZ8a07ApiNk5' where shl_id='1061002'

select count(*) from (select shl_id, st_id from xm_trad_flow where PLAT_TXN_DATE > '20240831' and PLAT_TXN_DATE < '20241231' and shl_id in ('10333','10610','10394','32058201','12789','10386','10393','11956','43038','13667','11053','11647','12867','10392','35021101','35012101','33021203','10316','13105','35020007','32011801','13106','12806','14160','32060027','32010021','14269','13945','14541','13288','32010019','14436','12792','10390','13383') and result='Y' group by shl_id, st_id having count(*) >= 2 )
;
    insert into yx_param(SHL_ID,APP_ID,PARAM_NO,PARAM_VALUE,PARAM_DESC,PARAM_TYPE,USING) values ('43100008','104001','BITMAP','1011101111111111111','首页权限位图','00','0');
insert into thdpty_App_info ( SHL_NM,SHL_ID, APP_ID, APP_NM, TITLE, CATEGORY, key, OPEN_FLG, PAY_CHECK_FLAG, RCRD_ID_WAY, CHK_COOKIE_FLAG,Open_Qb_flag,Title_nm,ASYN_SHY, rcrd_type,YKT_CZ_WAY,Offline_code_flag,Rcrd_type_offline)
values ('资兴市鲤鱼江中学','43100013','2019041263864463','支付宝小程序','0','01','DSHvta+vXSj96ByRxQB2p2qHtS2QIDAQAB','1','1','01','1','0','','0','00','1000000000','0','11');

insert into SCHL_MODEL_TB(ID,SHL_ID,APP_ID,MODEL_ID,Card_TPCD,MODEL_Wight) values(schlModelTb_Sequence.nextval,'43100008','104001','65','00','0');



select count(*) from xm_cust_info where createdate<'20241231' and shl_id in ('10333','10610','10394','32058201','12789','10386','10393','11956','43038','13667','11053','11647','12867','10392','35021101','35012101','33021203','10316','13105','35020007','32011801','13106','12806','14160','32060027','32010021','14269','13945','14541','13288','32010019','14436','12792','10390','13383');


insert into pay_merchant_info ( SHL_ID,mrch_id,mrch_name,counterno,mrch_tpcd,mrch_dpbkinno,pubkey,termno1,rsrv_fld_1) values
('14762','105000082115478','西安交通大学苏州附属中学','078464939','1','322000000','30819d300d06092a864886f70d010101050003818b003081870281810090f0ed9828bb2b9270cebf6250f7cc4c3e7565ce83825b63c18cfaaf0479325d8d97aab076cf308a43ce7fcb6ff99247c831efafb583f01a3364799a8e14fda666021d2e42870e1622c03a8fbb6eca8f23b6c6de52ce5122cff6c005b96bbe90f68fd03b3d87e5683da0e5621436e3423b6bf4b7ad0a80e8b87220028138b21f020111',
 '1050000821154780001','1');
insert into pay_merchant_info(SHL_ID,mrch_id,mrch_name,counterno,mrch_tpcd,mrch_dpbkinno,pubkey,rsrv_fld_1,termno1,pos_name)
values('11802','105000082202161','黔南民族医专','057710120','1','520000000','','1','1050000822021610001','青云湖超市1');

insert into pay_merchant_info ( SHL_ID,mrch_id,mrch_name,counterno,mrch_tpcd,mrch_dpbkinno,pubkey,termno1,rsrv_fld_1) values
('14762','105011958128923','防城港职业技术学院','093401127','1','450000000','30819d300d06092a864886f70d010101050003818b003081870281810098fec0af8efcdf4bb4c33949026878516bf81655ba9f5830cf87dab7892168c05a66e87a5a8247f7b3cfbb8d661679539f323602bebb872f8ffb36d6c83fda64f859f8a6b819906d1512ec5d349b6bbe34b0a7d028a70756dc4109fb4de1b262d90ed3e0bcfbf6c93daa975e93569e317fff15d8af9342313a23585e426c67d9020111'
,'1050119581289230001','1');

select INSPOS_ID from CCB_BOX_CONF where SCHOOL_ID=? and BOX_ID=? and MRCH_ID=? and CNTER_ID=? ;
select merch_id,pos_id,termno1,Tran_way,BRANCH_ID,merch_flag from mer_info where shl_id=? and term_id=? and device_status='';

insert into temp_user(shl_id,card_tpcd,cst_nm,mblph_np,rec_time,rec_opt,begin_dt,end_dt,cst_sts) values('1061002','02','','','20250911110000','18460368718','20250910','20260910','00');
insert into temp_user(shl_id,card_tpcd,cst_nm,mblph_np,rec_time,rec_opt,begin_dt,end_dt,cst_sts)
values('1061002','02','刘文文','13880973571','20250911110000','18460368718','20250910','20260910','00');
